﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using GenericTools;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.IO;
using DataModels.Common;
using DataModels.Enumerations;
using System.Text.RegularExpressions;

namespace ReportingManagement
{
    public class ExcelTemplateReport : IDisposable
    {
        #region Class Constants
        public const int PMTCT_MONTHLY_REPORT_ID = 1;
        public const int SAPR_APR_REPORT_ID = 2;

        public const string ANC_TABLE = "ANC";
        public const string PCR_TABLE = "PCR";
        public const string MAT_TABLE = "MAT";
        public const string MC_TABLE = "MC";
        public const string TC_TABLE = "TC";
        public const string FACILITY_INFO_TABLE = "INFO";

        private const string INVALID_OBJECT = @"Invalid object";

        public const int INVALID_TEMPLATE_REPORT_ID = -1;
        public const string INVALID_TEMPLATE_REPORT_NAME = "NOT USED";
        public const string INVALID_TEMPLATE_REPORT_DESCRIPTION = "NOT USED";
        public const string INVALID_TEMPLATE_REPORT_FILENAME = "NOT USED";
        #endregion

        #region Class Variables
        private int m_TemplateID;
        private string m_Name;
        private string m_Description;
        private string m_Filename;
        private bool m_MainTemplate;
        private bool m_UsedInSAPR;
        private bool m_UsedInAPR;
        private int? m_CalculatingMethod;
        private ExcelTemplateType m_ExcelTemplateType = ExcelTemplateType.NOT_SAPR_APR;
        
        private string m_OutputFile = null;
        private Excel.Worksheet m_ExcelSheet;
        private Excel.Sheets m_ExcelWorksheets;
        private Excel.Workbook m_ExcelWorkbook;
        private Excel.Workbooks m_ExcelWorkbooks;
        private Excel.Application m_ExcelApplication;

        private bool m_Valid;
        private DataSet m_DataSource;
        private bool m_DatasourceUpdated = false;
        private Dictionary<string, string> m_Values;
        private ExcelTemplateSheetCollection m_Sheets;

        private bool disposed = false;
        #endregion

        #region Constructors
        [Obsolete("Not used anymore", true)]
        public ExcelTemplateReport(int templateID)
        {
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                default:
                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    command = cmd;
                    break;
            }
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT TemplateID, TemplateName, Description, Filename, 
                MainTemplate, UsedInSAPR, UsedInAPR, CalculatingMethod
                FROM ExcelTemplateReport WHERE TemplateID = @TemplateID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@TemplateID";
            param.DbType = DbType.Int32;
            param.Value = templateID;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            if (dataTable.Rows.Count == 1) Initialize(dataTable);
            else m_Valid = false;
        }

        [Obsolete("Never used before, so why start now", true)]
        public ExcelTemplateReport(string name)
        {
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                default:
                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    command = cmd;
                    //command = GenericDataAccess.CreateCacheDBCommand();
                    break;
            }
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT TemplateID, TemplateName, Description, Filename, 
                MainTemplate, UsedInSAPR, UsedInAPR, CalculatingMethod 
                FROM ExcelTemplateReport WHERE Name = @Name";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Name";
            param.DbType = DbType.String;
            param.Value = name;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            if (dataTable.Rows.Count == 1) Initialize(dataTable);
            else m_Valid = false;
        }

        [Obsolete("Never used before, so why start now", true)]
        public ExcelTemplateReport(string filename, int i)
        {
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                default:
                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    command = cmd;
                    //command = GenericDataAccess.CreateCacheDBCommand();
                    break;
            }
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT TemplateID, TemplateName, Description, Filename, 
                MainTemplate, UsedInSAPR, UsedInAPR, CalculatingMethod
                FROM ExcelTemplateReport WHERE Filename = @Filename";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Filename";
            param.DbType = DbType.String;
            param.Value = filename;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            if (dataTable.Rows.Count == 1) Initialize(dataTable);
            else m_Valid = false;
        }

        private ExcelTemplateReport() { }
        #endregion

        #region Private Methods
        private void Initialize(DataTable data)
        {
            DataRow row = data.Rows[0];
            m_TemplateID = Int32.Parse(row["TemplateID"].ToString());
            m_Name = row["TemplateName"].ToString();
            m_Description = row["Description"].ToString();
            m_Filename = row["Filename"].ToString();
            m_MainTemplate = Boolean.Parse(row["MainTemplate"].ToString());
            m_UsedInSAPR = Boolean.Parse(row["UsedInSAPR"].ToString());
            m_UsedInAPR = Boolean.Parse(row["UsedInAPR"].ToString());
            
            int value;
            bool converted = int.TryParse(row["CalculatingMethod"].ToString(), out value);
            if (converted) m_CalculatingMethod = value;

            if (m_UsedInAPR) m_ExcelTemplateType |= ExcelTemplateType.APR;
            if (m_UsedInSAPR) m_ExcelTemplateType |= ExcelTemplateType.SAPR;

            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                default:
                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    command = cmd;
                    //command = GenericDataAccess.CreateCacheDBCommand();
                    break;
            }
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT SheetID FROM ExcelTemplateSheets WHERE TemplateID = @TemplateID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@TemplateID";
            param.DbType = DbType.Int32;
            param.Value = m_TemplateID;
            command.Parameters.Add(param);

            m_Sheets = new ExcelTemplateSheetCollection();
            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            foreach (DataRow r in dataTable.Rows)
            {
                int sheetId = Int32.Parse(r["SheetID"].ToString());
                ExcelTemplateSheet sheet = ExcelTemplateSheet.CreateExcelTemplateSheetFromDB(sheetId);
                m_Sheets.Add(sheet);
            }
            m_Values = new Dictionary<string, string>();
            m_Valid = true;
        }

        public void SimplifyDatasetFormat(bool prepareCellEvaluation = false)
        {
            if (m_DatasourceUpdated) return;

            m_Values.Clear();
            foreach (DataTable dataTable in m_DataSource.Tables)
            {
                string tableName = dataTable.TableName;
                switch (tableName)
                {
                    case ANC_TABLE:
                    case PCR_TABLE:
                    case MAT_TABLE:
                    case MC_TABLE:
                    case TC_TABLE:
                        foreach (DataRow row in dataTable.Rows)
                        {
                            string key = row[0].ToString(); // short code is index 0
                            string value = row[1].ToString();   // value is index 1
                            m_Values[key] = value;
                        }
                        break;

                    case FACILITY_INFO_TABLE:
                        DataRow r = dataTable.Rows[0];
                        foreach (DataColumn c in dataTable.Columns)
                        {
                            string k = c.ColumnName;
                            string v = r[c].ToString();
                            m_Values[k] = v;
                        }
                        break;
                }
            }
            m_DatasourceUpdated = true;

            if (prepareCellEvaluation)
            {
                foreach (ExcelTemplateSheet sheet in m_Sheets) 
                {
                    sheet.PrepareCellForEvaluation(m_Values);
                }
            }
        }
        #endregion

        #region Public Methods
        public void ReleaseExcelResources()
        {
            try
            {
                // Call the appropriate methods to clean up unmanaged resources here.
                if (m_ExcelApplication != null)
                {
                    m_ExcelWorkbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    m_ExcelApplication.Quit();

                    ReleaseObject(m_ExcelSheet);
                    ReleaseObject(m_ExcelWorksheets);
                    ReleaseObject(m_ExcelWorkbook);
                    ReleaseObject(m_ExcelWorkbooks);
                    ReleaseObject(m_ExcelApplication);
                }
            }
            catch { }
            finally
            {
                m_ExcelSheet = null;
                m_ExcelWorksheets = null;
                m_ExcelWorkbook = null;
                m_ExcelWorkbooks = null;
                m_ExcelApplication = null;
            }
        }

        public void WriteDataToExcelSheet(Excel.Worksheet excelSheet, string sheetName, 
            int columnOffset, int rowOffset, bool overwriteDataSource = true)
        {
            if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);

            foreach (ExcelTemplateSheet sheet in m_Sheets)
            {
                if (sheet.TemplateSheetName == sheetName)
                {
                    if (!m_DatasourceUpdated) SimplifyDatasetFormat();
                    sheet.WriteToExcelSheet(excelSheet, m_Values, columnOffset, rowOffset, overwriteDataSource);
                }
            }
        }

        public void WriteDataToExcelSheet(string sheetName,
            int columnOffset, int rowOffset, bool overwriteDataSource = true, 
            DataRow facilityInfo = null)
        {
            if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);
            if (String.IsNullOrEmpty(m_OutputFile)) throw new FileNotFoundException();

            if (m_ExcelApplication != null || m_ExcelSheet != null)
            {
                try
                {
                    if (m_ExcelSheet.Application != m_ExcelApplication) ReleaseExcelResources();
                }
                catch (System.Runtime.InteropServices.InvalidComObjectException)
                {
                    this.ReleaseExcelResources();
                }
            }

            if (m_ExcelApplication == null || m_ExcelSheet == null)
            {
                m_ExcelApplication = new Excel.Application();
                m_ExcelWorkbooks = m_ExcelApplication.Workbooks;
                m_ExcelWorkbook = m_ExcelWorkbooks.Open(m_OutputFile, 0, false, 5, "", "", true, 
                    Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                m_ExcelWorksheets = m_ExcelWorkbook.Worksheets;
                m_ExcelSheet = m_ExcelWorksheets[sheetName];
            }

            if (m_ExcelSheet.Name != sheetName)
            {
                ReleaseObject(m_ExcelSheet);
                m_ExcelSheet = m_ExcelWorksheets[sheetName];
            }

            foreach (ExcelTemplateSheet sheet in m_Sheets)
            {
                if (sheet.TemplateSheetName == sheetName)
                {
                    if (!m_DatasourceUpdated) SimplifyDatasetFormat();
                    sheet.WriteToExcelSheet(m_ExcelSheet, m_Values, columnOffset, rowOffset, overwriteDataSource, facilityInfo);
                }
            }
        }

        //public void SimplifyData

        //public static string ColumnNumberToLetter(int columnNumber)
        //{
        //    const int ASCII_CODE_OFFSET = 64;
        //    const int LETTERS_COUNT = 26;
        //    const string OVERFLOW = "Number system overflow";

        //    StringBuilder sBuilder = new StringBuilder();
        //    int asciiCode = -1;
        //    int repetition = 1;

        //    if (columnNumber > LETTERS_COUNT)
        //    {
        //        int currentDigit = 1;
        //        List<int> digits = new List<int>();
        //        do
        //        {
        //            digits.Add(currentDigit);
        //            columnNumber -= LETTERS_COUNT;
        //            currentDigit++;
        //            if (columnNumber <= LETTERS_COUNT)
        //            {
        //                asciiCode = currentDigit + ASCII_CODE_OFFSET;
        //                char c = (char)asciiCode;
        //                sBuilder.Append(c);
        //                break;
        //            }
        //            else
        //            {
        //                if (currentDigit == LETTERS_COUNT)
        //                {
        //                    asciiCode = currentDigit + ASCII_CODE_OFFSET;
        //                }
        //            }

        //        } while (true);
        //    }
            
        //    asciiCode = columnNumber + ASCII_CODE_OFFSET;
        //    char letter = (char)asciiCode;
        //    sBuilder.Append(letter);


        //    //do
        //    //{
        //    //    if (columnNumber >= LETTERS_COUNT)
        //    //    {
        //    //        int multiples = columnNumber % LETTERS_COUNT;


        //    //        if (repetition > LETTERS_COUNT) throw new InvalidOperationException(OVERFLOW);
        //    //        asciiCode = ASCII_CODE_OFFSET + repetition;
        //    //        repetition++;
        //    //    }
        //    //    else asciiCode = columnNumber + ASCII_CODE_OFFSET;

        //    //    char letter = (char)asciiCode;
        //    //    sBuilder.Append(letter);
        //    //    columnNumber -= LETTERS_COUNT;
        //    //} while (columnNumber < 1);
            
        //    string excelColumnName = sBuilder.ToString();
        //    return excelColumnName;
        //}

        //public static string ColumnNumberToLetter(int columnNumber, int a)
        //{
        //    string excelColumnName = String.Empty;
        //    const int LETTERS_COUNT = 26;
        //    List<int> digits = new List<int>();
        //    digits.Add(0);
        //    int currentPosition = 0;
        //    int currentValue = columnNumber;
        //    while (true)
        //    {
        //        if (currentPosition > LETTERS_COUNT)
        //        {
                    
        //        }
        //        else
        //        {

        //            break;
        //        }
        //    }

        //    return excelColumnName;
        //}

        public static string ColumnNumberToLetter(int ColumnNumber)
        {
            if (ColumnNumber > 26)
            {
                return ((char)(Math.Floor(((double)ColumnNumber - 1) / 26) + 64)).ToString()
                       + ((char)(((ColumnNumber - 1) % 26) + 65)).ToString();
            }
            return ((char)(ColumnNumber + 64)).ToString();
        }

        public static int GetColumnNumber(string excelCellAddress)
        {
            string columName = Regex.Replace(excelCellAddress, @"[\d]", string.Empty);

            int number = 0;
            int pow = 1;
            for (int i = columName.Length - 1; i >= 0; i--)
            {
                number += (columName[i] - 'A' + 1) * pow;
                pow *= 26;
            }

            return number;
        }

//        public static List<ExcelTemplateReport> GetExcelTemplateReports(ExcelTemplateType targetType, bool includeInvalidEntry = true)
//        {
//            DbCommand command = null;
//            switch (AppConfiguration.AccessMethod)
//            {
//                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
//                    command = GenericDataAccess.CreateCommand();
//                    break;

//                case DataAccessMethod.OFFLINE_DATA_ACCESS:
//                    command = GenericDataAccess.CreateCacheDBCommand();
//                    break;

//                default:
//                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
//                    OleDbConnection connection = new OleDbConnection(connectionString);
//                    OleDbCommand cmd = new OleDbCommand();
//                    cmd.Connection = connection;
//                    command = cmd;
//                    break;
//            }

//            command.CommandType = CommandType.Text;
//            command.CommandText = @"SELECT TemplateID, TemplateName, Description, Filename 
//                FROM ExcelTemplateReport";

//            var reports = new List<ExcelTemplateReport>();
//            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
//            foreach (DataRow r in dataTable.Rows)
//            {
//                int templateId = Int32.Parse(r["TemplateID"].ToString());
//                var template = new ExcelTemplateReport(templateId);
//                if (template.Valid)
//                {
//                    if ((template.ExcelTemplateType & targetType) == targetType) reports.Add(template);
//                }
//            }

//            if (includeInvalidEntry)
//            {
//                var invalidTemplate = new ExcelTemplateReport();
//                invalidTemplate.m_TemplateID = INVALID_TEMPLATE_REPORT_ID;
//                invalidTemplate.m_Name = INVALID_TEMPLATE_REPORT_NAME;
//                invalidTemplate.m_Description = INVALID_TEMPLATE_REPORT_DESCRIPTION;
//                invalidTemplate.m_Filename = INVALID_TEMPLATE_REPORT_FILENAME;
//                invalidTemplate.m_UsedInAPR = true;
//                invalidTemplate.m_UsedInSAPR = true;
//                invalidTemplate.m_Valid = true; // To suppress exception

//                reports.Add(invalidTemplate);
//            }
//            return reports;
//        }

        public static List<ExcelTemplateReport> GetExcelTemplateReports(List<DataModels.Models.ExcelTemplateReport> reports)
        {
            var result = new List<ExcelTemplateReport>();
            foreach (var r in reports)
            {
                var t = new ExcelTemplateReport();
                t.m_TemplateID = r.TemplateID;
                t.m_Name = r.TemplateName;
                t.m_Description = r.Description;
                t.m_Filename = r.Filename;
                t.m_MainTemplate = r.MainTemplate;
                t.m_UsedInAPR = r.UsedInAPR;
                t.m_UsedInSAPR = r.UsedInSAPR;
                t.m_CalculatingMethod = r.CalculatingMethod;
                t.m_Valid = true;
                t.m_Values = new Dictionary<string, string>();

                t.m_ExcelTemplateType = ExcelTemplateType.NOT_SAPR_APR;
                if (t.m_UsedInAPR) t.m_ExcelTemplateType |= ExcelTemplateType.APR;
                if (t.m_UsedInSAPR) t.m_ExcelTemplateType |= ExcelTemplateType.SAPR;
                
                t.m_Sheets = new ExcelTemplateSheetCollection();
                foreach (var sheet in r.ExcelTemplateSheets)
                {
                    var s = ExcelTemplateSheet.CreateExcelTemplateSheet(sheet);
                    t.m_Sheets.Add(s);
                }

                result.Add(t);
            }

            return result;
        }

        public static List<ExcelTemplateReport> GetExcelTemplateReports(ExcelTemplateType targetType, bool includeInvalidEntry)
        {
            var reports = Utilities.GetExcelTemplateReports();
            var excelReports = GetExcelTemplateReports(reports);
            var results = new List<ExcelTemplateReport>();
            foreach (var r in excelReports)
            {
                if ((r.ExcelTemplateType & targetType) == targetType) results.Add(r);
            }

            if (includeInvalidEntry)
            {
                var invalidTemplate = new ExcelTemplateReport();
                invalidTemplate.m_TemplateID = INVALID_TEMPLATE_REPORT_ID;
                invalidTemplate.m_Name = INVALID_TEMPLATE_REPORT_NAME;
                invalidTemplate.m_Description = INVALID_TEMPLATE_REPORT_DESCRIPTION;
                invalidTemplate.m_Filename = INVALID_TEMPLATE_REPORT_FILENAME;
                invalidTemplate.m_UsedInAPR = true;
                invalidTemplate.m_UsedInSAPR = true;
                invalidTemplate.m_Valid = true; // To suppress exception

                results.Add(invalidTemplate);
            }

            return results;
        }

        public string ToString()
        {
            return Description;
        }

        #endregion

        #region Public Properties
        public bool Valid
        {
            get
            {
                return m_Valid;
            }
        }

        public int TemplateID
        {
            get
            {
                if (m_Valid) return m_TemplateID;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public string Name
        {
            get
            {
                if (m_Valid) return m_Name;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public string Description
        {
            get
            {
                if (m_Valid) return m_Description;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public string Filename
        {
            get
            {
                if (m_Valid) return m_Filename;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public ExcelTemplateType ExcelTemplateType
        {
            get
            {
                if (m_Valid) return m_ExcelTemplateType;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public bool MainTemplate
        {
            get
            {
                if (m_Valid) return m_MainTemplate;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public bool UsedInSAPR
        {
            get
            {
                if (m_Valid) return m_UsedInSAPR;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public bool UsedInAPR
        {
            get
            {
                if (m_Valid) return m_UsedInAPR;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public DataSet DataSource
        {
            get
            {
                if (m_Valid) return m_DataSource;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set
            {
                if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);
                m_DataSource = value;
                m_DatasourceUpdated = false;
            }
        }

        public string OutputFile
        {
            get
            {
                if (m_Valid) return m_OutputFile;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set
            {
                if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);
                if (!File.Exists(value)) throw new FileNotFoundException("Output file must be existing file", value);
                m_OutputFile = value;
            }
        }

        public int? CalculatingMethod
        {
            get
            {
                if (m_Valid) return m_CalculatingMethod;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }

        public ExcelTemplateSheetCollection Sheets
        {
            get
            {
                if (m_Valid) return m_Sheets;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
        }
        #endregion

        /// <summary>
        /// Use interop to call the method necessary to clean up the unmanaged resource.
        /// </summary>
        /// <param name="handle"></param>
        /// <returns></returns>
        [System.Runtime.InteropServices.DllImport("Kernel32")]
        private extern static Boolean CloseHandle(IntPtr handle);

        /// <summary>
        /// Dispose(bool disposing) executes n two distinct scenarios. If disposing equals true, the method
        /// has been called directly or indirectly by a user's code. Managed and unmanaged resources can
        /// be disposed.
        /// If disposing equals false, the method has been called by the runtime from inside the finalizer
        /// and you should not reference other objects. Only unmanaged resources can be disposed.
        /// </summary>
        /// <param name="disposing"></param>
        private void Dispose(bool disposing)
        {
            // Check to see if Dispose has already been called.
            if (!this.disposed)
            {
                // if disposing equals true, dispose all managed and unmanaged resources.
                if (disposing)
                {
                    // Dispose managed resources.
                }

                // Call the appropriate methods to clean up unmanaged resources here.
                if (m_ExcelApplication != null)
                {
                    m_ExcelWorkbook.Close(true, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                    m_ExcelApplication.Quit();

                    ReleaseObject(m_ExcelSheet);
                    ReleaseObject(m_ExcelWorksheets);
                    ReleaseObject(m_ExcelWorkbook);
                    ReleaseObject(m_ExcelWorkbooks);
                    ReleaseObject(m_ExcelApplication);
                }                

                //CloseHandle(handle);
                //handle = IntPtr.Zero;
            }
        }

        /// <summary>
        /// Implements IDisposable.
        /// Do not make this method virtual. A derived class should not be able to override this method.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);

            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to take this object off the
            // finalization queue and prevent finalization code for this object from
            // executing a second time.
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// This destructor will run only if the Dispose method does not get called.
        /// It gives your base class the opportunity to finalize.
        /// Do not provide destructors in type derived from this class.
        /// </summary>
        ~ExcelTemplateReport()
        {
            // Do not re-create Dispose clean-up code here.
            // Calling Dispose(false) is optimal in terms of readability and maintanability.
            Dispose(false);
        }

        private void ReleaseObject(object obj)
        {
            try
            {
                if (obj == null) return;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            }
            finally
            {
                obj = null;
                GC.Collect();
            }
        }
    }
}
